import os
import pickle

import numpy as np
import pandas as pd
from tensorflow import keras

from src.laptop.model import laptop_model_config
from src.laptop.preprocessor.model_data_preprocessor import preprocess_model_data
from src.utils.config import logger
from src.utils.db_processor import  mysql_prediction_processor,presto_processor
from src.utils.util import get_today


COMPLETE_DATA_SQL = """
SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_id' AS property,
    cast(dp.product_id as VARCHAR) AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=5
)

UNION

SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_level_template_id' AS property,
	cast(CASE WHEN otpm.secondary_level_template_id IS NULL THEN -1 ELSE otpm.secondary_level_template_id END as VARCHAR) AS property_value
FROM dim.dim_product dp
JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id
WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=5
)

UNION

SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_sku_name' AS property,
    dps.product_sku_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=5
)

UNION

SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_name' AS property,
    dp.product_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=5
)

UNION

SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_brand_name' AS property,
    trim(dp.product_brand_name) AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=5
)
UNION

SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    (CASE
    WHEN pssp.property_name = '内存' THEN 'memory'
    WHEN pssp.property_name = '固态硬盘' THEN 'ssd'
    WHEN pssp.property_name = '国内保修情况' THEN 'guarantee'
    WHEN pssp.property_name = '处理器' THEN 'CPU'
    WHEN pssp.property_name = '处理器 ' THEN 'CPU'
    WHEN pssp.property_name = '机械硬盘' THEN 'hdd'
    WHEN pssp.property_name = '显卡' THEN 'GPU'
    WHEN pssp.property_name = '显卡 ' THEN 'GPU'
    WHEN pssp.property_name = '购买渠道' THEN 'purchase_way'
    WHEN pssp.property_name = '键盘' THEN 'keyboard'
    WHEN pssp.property_name = '颜色' THEN 'color'
    WHEN pssp.property_name = '屏幕类型' THEN 'screen'
    ELSE 'unknown_prop' END) AS property,
    pssp.property_value_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
    JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id
    AND pssp.property_name IN ('内存', '固态硬盘', '国内保修情况', '处理器', '机械硬盘', '显卡', '购买渠道', '键盘', '颜色', '屏幕类型','显卡 ', '处理器 ')

WHERE dp.product_category_id = 5  and dps.is_product_sku_active_flag=1
and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=5
)
"""


# COMPLETE_DATA_SQL = """
# SELECT
#     cast(dps.product_sku_id as int) as product_sku_id,
#     'product_id' AS property,
#     cast(dp.product_id as VARCHAR) AS property_value
# FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
# inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
# WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
# and dps.product_sku_id in (
# 3847992,3228092,5345038,6780776,4211915,3634733,155762,6730118,6533894,3367199,2481959,6952866
# )
#
# UNION
#
# SELECT
#     cast(dps.product_sku_id as int) as product_sku_id,
#     'product_level_template_id' AS property,
# 	cast(CASE WHEN otpm.secondary_level_template_id IS NULL THEN -1 ELSE otpm.secondary_level_template_id END as VARCHAR) AS property_value
# FROM dim.dim_product dp
# JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
# JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id
# WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
# and dps.product_sku_id in (
# 3847992,3228092,5345038,6780776,4211915,3634733,155762,6730118,6533894,3367199,2481959,6952866
# )
#
# UNION
#
# SELECT
#     cast(dps.product_sku_id as int) as product_sku_id,
#     'product_sku_name' AS property,
#     dps.product_sku_name AS property_value
# FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
# inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
# WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
# and dps.product_sku_id in (
# 3847992,3228092,5345038,6780776,4211915,3634733,155762,6730118,6533894,3367199,2481959,6952866
# )
#
# UNION
#
# SELECT
#     cast(dps.product_sku_id as int) as product_sku_id,
#     'product_name' AS property,
#     dp.product_name AS property_value
# FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
# inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
# WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
# and dps.product_sku_id in (
# 3847992,3228092,5345038,6780776,4211915,3634733,155762,6730118,6533894,3367199,2481959,6952866
# )
#
# UNION
#
# SELECT
#     cast(dps.product_sku_id as int) as product_sku_id,
#     'product_brand_name' AS property,
#     trim(dp.product_brand_name) AS property_value
# FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
# inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
# WHERE dp.product_category_id = 5   and dps.is_product_sku_active_flag=1
# and dps.product_sku_id in (
# 3847992,3228092,5345038,6780776,4211915,3634733,155762,6730118,6533894,3367199,2481959,6952866
# )
# UNION
#
# SELECT
#     cast(dps.product_sku_id as int) as product_sku_id,
#     (CASE
#     WHEN pssp.property_name = '内存' THEN 'memory'
#     WHEN pssp.property_name = '固态硬盘' THEN 'ssd'
#     WHEN pssp.property_name = '国内保修情况' THEN 'guarantee'
#     WHEN pssp.property_name = '处理器' THEN 'CPU'
#     WHEN pssp.property_name = '处理器 ' THEN 'CPU'
#     WHEN pssp.property_name = '机械硬盘' THEN 'hdd'
#     WHEN pssp.property_name = '显卡' THEN 'GPU'
#     WHEN pssp.property_name = '显卡 ' THEN 'GPU'
#     WHEN pssp.property_name = '购买渠道' THEN 'purchase_way'
#     WHEN pssp.property_name = '键盘' THEN 'keyboard'
#     WHEN pssp.property_name = '颜色' THEN 'color'
#     WHEN pssp.property_name = '屏幕类型' THEN 'screen'
#     ELSE 'unknown_prop' END) AS property,
#     pssp.property_value_name AS property_value
# FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
# inner join ods.ods_foundation_data_sku  sku on dps.product_sku_id =sku.id  and sku.business_channel='0'
#     JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id
#     AND pssp.property_name IN ('内存', '固态硬盘', '国内保修情况', '处理器', '机械硬盘', '显卡', '购买渠道', '键盘', '颜色', '屏幕类型','显卡 ', '处理器 ')
#
# WHERE dp.product_category_id = 5  and dps.is_product_sku_active_flag=1
# and dps.product_sku_id in (
# 3847992,3228092,5345038,6780776,4211915,3634733,155762,6730118,6533894,3367199,2481959,6952866
# )
# """




#
# LEVEL_SQL = """
# SELECT
# product_level_id as level_id,
# product_level_name
# FROM dim.dim_product_level
# WHERE product_category_id = 5 AND is_product_level_active_flag = 1
# """

LEVEL_SQL = """
SELECT
cast(product_level_id as int) as level_id,
product_level_name
FROM dim.dim_product_level
WHERE product_category_id = 5 AND is_product_level_active_flag = 1
AND product_level_name in ({})
""".format("'" + "','".join(lvl for lvl in laptop_model_config.NEW_LEVEL_COEF['product_level_name']) + "'")

# LEVEL_TEMPLATE_MAPPING_SQL = """
# SELECT
# cast(level_template_id as int) AS product_level_template_id,
# cast(level_id  as int) as level_id
# FROM dim.dim_product_level_template_mapping
#
# """

#二级等级模板
LEVEL_TEMPLATE_MAPPING_SQL = """
select secondary_level_template_id as product_level_template_id,
product_level_id as level_id
from  ods.ods_opt_foundation_secondary_product_level_template_level
"""

# LEVEL_TEMPLATE_MAPPING_SQL = """
# SELECT
# level_template_id AS product_level_template_id,
# level_id as level_id
# FROM dim.dim_product_level_template_mapping
# """





BRAND_ID_NAME_SQL = """
SELECT distinct
    cast(dps.product_brand_id as int) as product_brand_id,
    trim(dp.product_brand_name) as product_brand_name
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
WHERE dp.product_category_id in (1,5,6)  and dps.is_product_sku_active_flag=1
"""

query_sku_sql="""
select product_sku_id from tmp_laptop_evaluate_sku
"""


predict_data_sql = '''
  select
  a.product_sku_key,a.level_id,a.document_item_id,
  a.secondary_sku_id,a.secondary_level_id,item_quotation_price_num,forecast_reference_price_to_c,a.create_date
  from
  (
      select
      cdp.document_item_id,
	  cast(dps.product_sku_id as int) AS product_sku_key,
	  cdp.product_combine_level_id AS level_id,
      sku2level.secondary_level_id, --二级level,c2b使用
      sku2level.secondary_sku_id, --二级skuid,c2b使用
      cast(psdp.settle_amount_num as int) AS item_quotation_price_num,
      cdp.create_date
      from
      dw.dw_centre_document_product cdp
      JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
      JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
	  JOIN dim.dim_product_sku dps ON dps.product_sku_id = cdp.product_combine_sku_id
      JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
      join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on pdp.product_no =sku2level.product_no
      left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
	  AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date=cast('{0}' as date)  

      WHERE cdp.document_category_id >= 200 
      AND cdp.document_item_status_id IN (201, 202, 204) 
      AND psdp.recycler_id NOT IN (12599, 507, 286)
      AND pdp.quotation_document_type_id = 10  
      AND cdp.create_date=cast('{0}' as date)
      and psdp.settle_status_id not in (-1,5,9)
      and psdp.start_price>0
      and dp.product_category_id=5
      and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
  )a inner join
  (
      select 
      product_sku_key,product_level_key,forecast_reference_price_to_c
      from 
      ods.ods_price_prediction_price_prediction 
      where 
      predict_date='{0}'
      and product_category_id=5
  )b on a.secondary_sku_id=b.product_sku_key and a.secondary_level_id=b.product_level_key 
'''








class LaptopSKUComplement:
    def __init__(self, model_date=None):
        if model_date is None:
            self.model_date = get_today()
        else:
            self.model_date = model_date
        self.predict_data = None

        self.__load_models()
        self.__load_attr_default_price()

    def __load_models(self):
        """
        读取最新的模型与预处理器
        :return:
        """
        logger.info('loading laptop complement models...')
        model_file = os.path.join(laptop_model_config.MODEL_DIR, laptop_model_config.MODEL_FILE_NAME)
        self.model = keras.models.load_model(model_file)

        ohe_product_file = 'ohe_product' + laptop_model_config.PREPROCESSOR_SUFFIX_NAME
        with open(os.path.join(laptop_model_config.MODEL_DIR, ohe_product_file), 'rb') as f:
            self.ohe_product = pickle.load(f)

        ohe_level_file = 'ohe_level' + laptop_model_config.PREPROCESSOR_SUFFIX_NAME
        with open(os.path.join(laptop_model_config.MODEL_DIR, ohe_level_file), 'rb') as f:
            self.ohe_level = pickle.load(f)

        ohe_attr_file = 'ohe_attr' + laptop_model_config.PREPROCESSOR_SUFFIX_NAME
        with open(os.path.join(laptop_model_config.MODEL_DIR, ohe_attr_file), 'rb') as f:
            self.ohe_attr = pickle.load(f)

        ohe_period_file = 'ohe_period' + laptop_model_config.PREPROCESSOR_SUFFIX_NAME
        with open(os.path.join(laptop_model_config.MODEL_DIR, ohe_period_file), 'rb') as f:
            self.ohe_period = pickle.load(f)

        scaler_file = 'scaler' + laptop_model_config.PREPROCESSOR_SUFFIX_NAME
        with open(os.path.join(laptop_model_config.MODEL_DIR, scaler_file), 'rb') as f:
            self.scaler = pickle.load(f)

        logger.info('loading laptop complement models done')

    def __load_attr_default_price(self):
        """
        读取没有属性配置信息笔记本电脑的默认价格
        :return:
        """
        logger.info('loading default price...')
        sql = "SELECT product_level_name, actual_price as default_price FROM laptop_attr_default_price"
        self.attr_default_price = mysql_prediction_processor.load_sql(sql)

    def process_complement_data(self):
        """
        处理补全数据
        :return:
        """
        logger.info('processing complement data...')
        data = self.__load_complement_data()

        data = data.drop_duplicates(['product_sku_id', 'property'])
        data['property'] = data['property'].map(lambda x: x.strip())
        data['property_value'] = data['property_value'].map(lambda x: x.strip())
        data = data.pivot_table(index='product_sku_id', columns='property', values='property_value',
                                aggfunc=lambda x: x).reset_index()
        data.fillna('unknown', inplace=True)

        levels = self.__load_product_levels()

        # 做笛卡尔积
        data['_tmp_key'] = 0
        levels['_tmp_key'] = 0

        data = pd.merge(data, levels, on='_tmp_key', how='outer').drop(columns='_tmp_key')

        data = preprocess_model_data(data, is_cal_period=False)
        data['period'] = '0'
        data['product_category_id'] = 5
        data['product_category_name'] = '笔记本'
        self.predict_data = data
        self.predict_data['date'] = self.model_date.strftime('%Y-%m-%d')

    def predict_complement_data(self):
        """
        预测补全数据
        :return:
        """
        if self.predict_data is None:
            logger.critical('predict_data is None!')
            return

        logger.info('predicting complement data...')
        x_predict_product = self.ohe_product.transform(self.predict_data[laptop_model_config.PRODUCT_FEATURES])
        x_predict_level = self.ohe_level.transform(self.predict_data[laptop_model_config.LEVEL_FEATURES])
        x_predict_attr = self.ohe_attr.transform(self.predict_data[laptop_model_config.ATTR_FEATURES])
        x_predict_period = self.ohe_period.transform(self.predict_data[laptop_model_config.PERIOD_FEATURES])

        predict_scaled = self.model.predict([x_predict_product, x_predict_level, x_predict_attr, x_predict_period])
        self.predict_data['forecast_reference_price'] = np.round(
            self.scaler.inverse_transform(predict_scaled).flatten()).astype(int)
        self.predict_data['forecast_reference_price'] = self.predict_data['forecast_reference_price'].where(
            self.predict_data['forecast_reference_price'] >= 5, 5)

        # 对无属性信息的产品使用默认价格
        if self.attr_default_price is not None:
            is_no_attr = (self.predict_data[laptop_model_config.NO_ATTR_CHECK_LIST].isin(
                ['unknown', 'APPLE_HDD', 'MS_HDD'])).all(axis=1)
            predict_data = pd.merge(self.predict_data, self.attr_default_price, on='product_level_name', how='left')
            predict_data['default_price'].fillna(5, inplace=True)
            predict_data.loc[is_no_attr, 'price'] = predict_data.loc[is_no_attr, 'default_price']

    # def save_complement_data(self):
    #     """
    #     保存补全数据
    #     :return:
    #     """
    #     if self.predict_data is not None:
    #         logger.info('saving complement data...')
    #         template_level_mapping = self.__load_template_level_mapping()
    #         insert_data = pd.merge(self.predict_data, template_level_mapping,
    #                                on=['product_level_template_id', 'level_id'])
    #         insert_sql = """
    #         INSERT INTO dm.dm_prognostizieren_preisergebnisse(predict_date, product_sku_key, product_sku_name,
    #         product_level_key, product_level_name, product_key, product_name, product_category_id,
    #         product_category_name, product_brand_name, forecast_reference_price)
    #         VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    #         """
    #         postgre_processor.execute_sql(
    #             "SELECT md.add_partition('dm.dm_prognostizieren_preisergebnisse','{}','day')".format(
    #                 self.model_date.strftime('%Y%m%d')))
    #         postgre_processor.execute_insert_postgresql(insert_sql,
    #                                                      insert_data[
    #                                                          ['date', 'product_sku_id', 'product_sku_name', 'level_id',
    #                                                           'product_level_name', 'product_id', 'product_name',
    #                                                           'product_category_id', 'product_category_name',
    #                                                           'product_brand_name', 'forecast_reference_price']
    #                                                      ].to_records(index=False).tolist())
    #         logger.info('saving complement data done')

    def __load_complement_data(self):
        """
        读取测试数据
        :return:
        """
        return presto_processor.load_sql(COMPLETE_DATA_SQL)

    def __load_product_levels(self):
        """
        读取等级
        :return:
        """
        return presto_processor.load_sql(LEVEL_SQL)

    def __load_template_level_mapping(self):
        """
        读取等级模板和等级对应关系
        :return:
        """
        mapping = presto_processor.load_sql(LEVEL_TEMPLATE_MAPPING_SQL)
        mapping['product_level_template_id'] = mapping['product_level_template_id'].astype(str)
        mapping['level_id'] = mapping['level_id'].astype(int)

        return mapping
